This invention relates to a computer system that includes a database management system for operating a database. More specifically, this invention relates to a technique of selecting an optimum access plan.
Today, business and other organizations store an enormous amount of data in a database (DB), and a database management system (DBMS), which collects data from a DB plays an important role in running of such an organization.
In general, a DBMS operates on a server. The server is connected to a storage system and stores DB data in the connected storage system.
A DBMS performs the following processing:
When receiving an inquiry from a client, a DBMS first conducts optimization processing. Optimization processing is processing executed to select an access plan that is most efficient in executing the inquiry made by the client. The DBMS then accesses a DB based on the chosen access plan.
Further, there are known two types of optimization processing: rule-based optimization processing and cost-based optimization processing.
In the rule-based optimization processing, an access plan is determined based on how a code (SQL statement) used by a client to make an inquiry is stated or the like. In the cost-based optimization processing, an access plan is determined based on such information as the speed of CPU (CPU cost) of the server on which the DBMS operates.
A client has to have considerable knowledge to enable a DBMS to employ rule-based optimization processing. It is therefore common for a DBMS to employ cost-based optimization processing.
An example of optimization processing employed by a DBMS is disclosed in Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahes, Miso Cilimdzic: “Robust query processing through progressive optimization”, Proc. Of the 2004 ACM SIGMOD International Conference on Management of Data, 2004, p. 659-670.
According to this publication, a technique is known which changes an access plan during execution of the access plan if there is another executable access plan that is lower in cost.
The storage system receives a request (write request or read request) from the server to which it is connected and on which the DBMS operates, and processes the request as follows:
The storage system has a disk drive and a cache memory. The disk drive is a non-volatile storage medium (e.g., hard disk drive) where data is stored. The cache memory is a volatile storage medium (e.g., semiconductor memory) where data is stored temporarily.
Upon reception of a read request from the server, the storage system judges whether requested data is stored in the cache memory or not.
When the requested data is stored in the cache memory, the storage system reads the data out of the cache memory and sends the read data to the server, whereby the processing is ended.
Generally speaking, data is read out of a cache memory quicker than out of a disk drive. A storage system therefore can respond to a request of a server faster by reading data out of a cache memory instead of a disk drive.
Usually, a storage system has one cache memory and is connected to plural servers. The cache memory in this case is shared among the plural servers connected to the storage system. How much of the cache memory each of the servers is allowed to use is not regulated.
For that reason, when one of the servers makes a request to read a large amount of data, the cache memory is used up by this server and applications of other servers that share the cache memory are lowered in performance.
This problem can be solved by a technique disclosed in an internet article, Hitachi TagmaStore Universal Storage Platform (URL: http://www.hds.com/pdf/wp168_tagmastore_virtualization.pdf).
This technique allocates a part of the memory area of a cache memory to each server, which uses the allocated area exclusively.
JP 2004-295790 A discloses a technique with which a part of the memory area of a cache memory that is allocated to one server can be reallocated to another server. This technique allows a server to have a part of the memory area of a cache memory that is allocated to another server when an application in the former server fails to meet a required performance level. With an additional portion of the memory area provided by another server, the application in the former server can now meet the required performance level.